﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySQLStudio.DataAccess;
using MySQLStudio.Common;


namespace MySQLStudio.BusinessLogic
{
    public class ViewController
    {
        public static List<View> GetViewsInDataBase(string connectionString, string databaseName)
        {
            IDataReader reader = null;
            List<View> lstViews = new List<View>();

            try
            {
                reader = MySqlDataAccess.ExecuteDataReader(connectionString, InformationSchemaHelper.GetViewDatabaseCommand(databaseName));

                lstViews = new List<View>();

                while (reader.Read())
                {
                    //TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION
                    lstViews.Add(new View( Convert.ToString(reader["TABLE_NAME"]),
                                                               Convert.ToString(reader["TABLE_SCHEMA"]),
                                                               String.Empty,
                                                               new DateTime(),
                                                               new DateTime(),
                                                               Convert.ToString(reader["VIEW_DEFINITION"]),
                                                               connectionString
                                                              ));
                }

            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_extract_stored_procedures_in_database, databaseName), ex);
            }
            finally
            {
                if (reader != null)
                {
                    if (!reader.IsClosed)
                        reader.Close();
                }
            }

            return lstViews;
        }
    
//USE test;
//CREATE VIEW `test`.`new_view3` AS
//SELECT qty, qty*price AS value FROM t;
        public static String Execute(string connectionString, String sqlQuery)
        {
            String retVal = String.Empty;

            IDataReader reader = null;

            try
            {
                reader = MySqlDataAccess.ExecuteDataReader(connectionString, sqlQuery);
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine("on Execute" + ex.Message);
               // throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_extract_stored_procedures_in_database, databaseName), ex);
            }
            finally
            {
                if (reader != null)
                {
                    if (!reader.IsClosed)
                        reader.Close();
                }
            }

            return retVal;
        }

       public static String GetViewDropStatement(string connectionString, String viewNameToDrop)
       {
           String retVal = String.Empty;
           Execute(connectionString, InformationSchemaHelper.GetViewDropStatementCommand(viewNameToDrop));

           return retVal;
       }

       public static ColumnCollection GetColumnsStatement(String connectionString, String schemaName, String viewName)
       {
           IDataReader reader = null;
           ColumnCollection lstColumnsInViews = new ColumnCollection();

           try
           {
               reader = MySqlDataAccess.ExecuteDataReader(connectionString, InformationSchemaHelper.GetViewColumnsDataBaseCommand(schemaName, viewName));

               while (reader.Read())
               {
                   Column column = new Column();
                   
                   String name = Convert.ToString(reader["COLUMN_NAME"]);

                   if (name != null)
                   {
                       column.Name = name;
                       lstColumnsInViews.Add(column);
                   }
               }

           }
           catch (Exception ex)
           {
               Console.Out.WriteLine("on GetColumnsStatement" + ex.Message);
           }
           finally
           {
               if (reader != null)
                   reader.Close();
           }

           return lstColumnsInViews;
       }

       public static String GetAlterViewInDataBase(string connectionString, String schemaName, String viewName)
       {
           IDataReader reader = null;
           String retVal = String.Empty;

           try
           {
               reader = MySqlDataAccess.ExecuteDataReader(connectionString,
                                InformationSchemaHelper.GetCreateViewStatementCommand(schemaName, viewName));
               
               if (reader.Read())
               {
                   retVal = Convert.ToString(reader["Create view"]);
               }

           }
           catch (Exception ex)
           {
               throw new ApplicationException(ErrorMessages.GetString("Get Create View", schemaName + " " + viewName), ex);
           }
           finally
           {
               if (reader != null)
               {
                   if (!reader.IsClosed)
                       reader.Close();
               }
           }

           return retVal;
       }
    }
}
